package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.wms.WMSPickDemandItemStock;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class WMSPickDemandItemStockDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSPickDemandItemStockDAO.class);

    private static WMSPickDemandItemStockDAO Instance = null;

    private WMSPickDemandItemStockDAO() {
        super();
    }

    public static WMSPickDemandItemStockDAO getInstance() {
        if (Instance == null)
            Instance = new WMSPickDemandItemStockDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSPickDemandItemStock wWMSPickDemandItemStock,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSPickDemandItemStock == null)
                return 0;

            String wSQL;
            if (wWMSPickDemandItemStock.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_pickdemanditemstock(TaskID,PickedNumber,BatchNumber," +
                                "Stock,Supplier,ProductName,SerialNo,ProductTime,StockPosition,ProjectStockNo,ExpirationDate,WarehousingDate,AssessmentType,SupplierLotNumber) VALUES" +
                                "(:TaskID," +
                                ":PickedNumber," +
                                ":BatchNumber,:Stock,:Supplier,:ProductName,:SerialNo,:ProductTime,:StockPosition," +
                                ":ProjectStockNo,:ExpirationDate,:WarehousingDate,:AssessmentType,:SupplierLotNumber);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.wms_pickdemanditemstock SET TaskID = :TaskID,PickedNumber = " +
                                ":PickedNumber,BatchNumber = :BatchNumber,Stock = :Stock,Supplier = :Supplier," +
                                "ProductName = :ProductName,SerialNo = :SerialNo,ProductTime = :ProductTime," +
                                "StockPosition=:StockPosition,ProjectStockNo=:ProjectStockNo,ExpirationDate=:ExpirationDate," +
                                "WarehousingDate=:WarehousingDate,AssessmentType=:AssessmentType,SupplierLotNumber=:SupplierLotNumber WHERE ID " +
                                "=" +
                                " :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSPickDemandItemStock.ID);
            wParamMap.put("TaskID", wWMSPickDemandItemStock.TaskID);
            wParamMap.put("PickedNumber", wWMSPickDemandItemStock.PickedNumber);
            wParamMap.put("BatchNumber", wWMSPickDemandItemStock.BatchNumber);
            wParamMap.put("Stock", wWMSPickDemandItemStock.Stock);
            wParamMap.put("Supplier", wWMSPickDemandItemStock.Supplier);
            wParamMap.put("ProductName", wWMSPickDemandItemStock.ProductName);
            wParamMap.put("SerialNo", wWMSPickDemandItemStock.SerialNo);
            wParamMap.put("ProductTime", wWMSPickDemandItemStock.ProductTime);
            wParamMap.put("StockPosition", wWMSPickDemandItemStock.StockPosition);
            wParamMap.put("ProjectStockNo", wWMSPickDemandItemStock.ProjectStockNo);
            wParamMap.put("ExpirationDate", wWMSPickDemandItemStock.ExpirationDate);
            wParamMap.put("WarehousingDate", wWMSPickDemandItemStock.WarehousingDate);
            wParamMap.put("AssessmentType", wWMSPickDemandItemStock.AssessmentType);
            wParamMap.put("SupplierLotNumber", wWMSPickDemandItemStock.SupplierLotNumber);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSPickDemandItemStock.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSPickDemandItemStock.setID(wResult);
            } else {
                wResult = wWMSPickDemandItemStock.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSPickDemandItemStock> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSPickDemandItemStock wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_pickdemanditemstock WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSPickDemandItemStock SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSPickDemandItemStock wResult = new WMSPickDemandItemStock();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSPickDemandItemStock> wList = SelectList(wLoginUser, wID, -1, "", "", wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSPickDemandItemStock> SelectList(BMSEmployee wLoginUser, int wID, int wTaskID, String wBatchNumber,
                                                   String wSerialNo, OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItemStock> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT * FROM {0}.wms_pickdemanditemstock WHERE  1=1  and ( :wID <= 0 or :wID = ID ) and ( " +
                            ":wTaskID <= 0 or :wTaskID = TaskID ) and ( :wBatchNumber is null or :wBatchNumber = ''''" +
                            " " +
                            "or :wBatchNumber = BatchNumber ) and ( :wSerialNo is null or :wSerialNo = '''' or " +
                            ":wSerialNo = SerialNo );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wTaskID", wTaskID);
            wParamMap.put("wBatchNumber", wBatchNumber);
            wParamMap.put("wSerialNo", wSerialNo);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItemStock wItem = new WMSPickDemandItemStock();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.TaskID = StringUtils.parseInt(wReader.get("TaskID"));
                wItem.PickedNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));
                wItem.ExpirationDate = StringUtils.parseString(wReader.get("ExpirationDate"));
                wItem.WarehousingDate = StringUtils.parseString(wReader.get("WarehousingDate"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.SupplierLotNumber = StringUtils.parseString(wReader.get("SupplierLotNumber"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    public List<WMSPickDemandItemStock> SelectListByItemIDList(BMSEmployee wLoginUser, List<Integer> wItemIDList,
                                                               OutResult<Integer> wErrorCode) {
        List<WMSPickDemandItemStock> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wItemIDList == null || wItemIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT * FROM {0}.wms_pickdemanditemstock WHERE  1=1  " +
                            "and (  TaskID in ({1}) ) ;",
                    wInstance.Result,StringUtils.Join(",",wItemIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            for (Map<String, Object> wReader : wQueryResult) {
                WMSPickDemandItemStock wItem = new WMSPickDemandItemStock();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.TaskID = StringUtils.parseInt(wReader.get("TaskID"));
                wItem.PickedNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ProjectStockNo = StringUtils.parseString(wReader.get("ProjectStockNo"));
                wItem.ExpirationDate = StringUtils.parseString(wReader.get("ExpirationDate"));
                wItem.WarehousingDate = StringUtils.parseString(wReader.get("WarehousingDate"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.SupplierLotNumber = StringUtils.parseString(wReader.get("SupplierLotNumber"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }
}
